package com.hhf.ds.util;

import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
public class ImportUtils {
    private final static Integer flushCount = 1000;

    public static void importExcel(String filePath, String rptCode) throws IOException, InvalidFormatException {
        // 校验文件后缀是否为.xlsx，读取的文件是否存在
        File file = new File(filePath);
        OPCPackage pkg = OPCPackage.open(file, PackageAccess.READ);
        XSSFWorkbook wb = new XSSFWorkbook(pkg);

        DataFormatter formatter = new DataFormatter();
        List<Map<Integer, String>> list = new ArrayList<>();
        for (Sheet sheet : wb) {
            for (Row row : sheet) {
                Map<Integer, String> map = new HashMap(16);
                for (Cell cell : row) {
                    CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                    String text = formatter.formatCellValue(cell);
                    log.debug(cellRef.formatAsString() + ":"+ text);
                    map.put(cell.getColumnIndex(), text);
                }
                list.add(map);
                if(list.size() % flushCount == 0) {
                    // TODO 1、入库处理

                    // 2、手动清空list
                    list.clear();
                }
            }

        }
        wb.close();
        pkg.close();

    }

    public static List<List<String>> allExcelData(String filePath) throws IOException {
        // TODO 校验文件是否为xlsx，读取的文件是否存在
        OPCPackage pkg = null;
        XSSFWorkbook wb = null;
        List<List<String>> rowlist = new ArrayList<>();
        DataFormatter formatter = new DataFormatter();
        try {
            File file = new File(filePath);
            pkg = OPCPackage.open(file);
            wb = new XSSFWorkbook(pkg);
            for (Sheet sheet : wb) {
                for (Row row : sheet) {
                    List<String> cellList = new LinkedList();
                    for (Cell cell : row) {
                        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                        String text = formatter.formatCellValue(cell);
                        log.debug(cellRef.formatAsString() + ":"+ text);
                        cellList.add(text);
                    }
                    rowlist.add(cellList);
                }
            }
        } catch (InvalidFormatException e) {
            log.error(e.getMessage(), e);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            if(wb != null) {
                wb.close();
            }
            if(pkg != null) {
                pkg.close();
            }
        }
        return rowlist;
    }

    public static String getSelectColNames(String sql) throws JSQLParserException {
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        PlainSelect plain = (PlainSelect) select.getSelectBody();
        List<SelectItem> selectitems = plain.getSelectItems();
        List<String> items = selectitems.stream().map(item -> item.toString()).collect(Collectors.toList());
        return String.join(",", items);
    }

    public static List<String> getSelectTables (String sql) throws JSQLParserException {
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        List<String> tableList = tablesNamesFinder.getTableList(select);
        return tableList;
    }

    public static String extractorXlsText(String filePath) throws IOException {
        POIFSFileSystem fs = null;
        String text = null;
        try {
            fs = new POIFSFileSystem(new File(filePath));
            HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
            ExcelExtractor extractor = new ExcelExtractor(wb);
            extractor.setFormulasNotResults(true);
            extractor.setIncludeSheetNames(false);
            text = extractor.getText();
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            fs.close();
        }
        return text;
    }

}
